/*
 * db_helper.c
 *
 *  Created on: May 13, 2016
 *      Author: Alan Wang
 */
#include <sqlite3.h>
#include <stdio.h>
#include<stdlib.h>
#include <string.h>
#include "db_helper.h"
#include "../../util/log.h"

#define PATH_OF_WIFIINFO_DB		"src/business/sqldb/wifiinfo.db"
#define NAME_OF_WIFIINFO_TABLE	"WIFIINFO"

int l_is_wifiinfo_exist(sqlite3 *db, char *mac);
int l_db_update_wifiinfo(sqlite3 *db, WifiInfo wifiinfo);
int l_db_insert_wifiinfo(sqlite3 *db, WifiInfo wifiinfo);
int l_db_wifiinfo_execute_sql(char *sql);

int db_init_wifiinfoDB(){
	char sql[512];
	sprintf(sql, "CREATE TABLE %s("
			"ID INTEGER PRIMARY KEY, "
			"WIFI_ID INTEGER, "
			"MAST_ID INTEGER, "
			"SSID VARCHAR(%d),"
			"PASSWORD VARCHAR(%d),"
			"MAC VARCHAR(%d),"
			"LONGITUDE REAL, "
			"LATITUDE REAL,"
			"AREA_ID INTEGER);",
			NAME_OF_WIFIINFO_TABLE, MAX_SSID_LEN, MAX_PWD_LEN, MAX_MAC_LEN);
	return l_db_wifiinfo_execute_sql(sql);
}

int db_is_wifiinfo_exist(char *mac){
	int ret = 0;
	sqlite3 *db = NULL;
	ret = sqlite3_open(PATH_OF_WIFIINFO_DB, &db); //open the wifiinfo database
	if(ret != SQLITE_OK){
		Log("db_is_wifiinfo_exist()---->>>open db %s failed!", PATH_OF_WIFIINFO_DB);
		sqlite3_close(db);
		return NULL;
	}
	Log("db_is_wifiinfo_exist()---->>>>open db %s success!\n", PATH_OF_WIFIINFO_DB);
	return l_is_wifiinfo_exist(db, mac);
}

int db_insert_wifilist(WifiInfo *wifilist, int num){
	int ret = 0;
	sqlite3 *db = NULL;
	ret = sqlite3_open(PATH_OF_WIFIINFO_DB, &db); //open the wifiinfo database
	if(ret != SQLITE_OK){
		Log("db_insert_wifilist()---->>>open db %s failed!", PATH_OF_WIFIINFO_DB);
		sqlite3_close(db);
		return NULL;
	}
	Log("db_insert_wifilist()---->>>>open db %s success!\n", PATH_OF_WIFIINFO_DB);
	int i;
	for(i = 0; i < num; i++){
		ret = l_is_wifiinfo_exist(db, wifilist[i].wifi_mac);
		if(ret == 1){//the wifiinfo have been in the table,excute updata operation
			l_db_update_wifiinfo(db, wifilist[i]);
		}else{//the wifiinfo have been not in the table,excute insert operation
			l_db_insert_wifiinfo(db, wifilist[i]);
		}
	}
	sqlite3_close(db);
	return 1;
}

int db_select_wifilist(DBWifiInfo *dbWifiList, int last_id, int count){
	int ret = 0;
	sqlite3 *db = NULL;
	ret = sqlite3_open(PATH_OF_WIFIINFO_DB, &db); //open the wifiinfo database
	if(ret != SQLITE_OK){
		Log("db_select_wifilist()---->>>open db %s failed!", PATH_OF_WIFIINFO_DB);
		sqlite3_close(db);
		return NULL;
	}
	Log("db_select_wifilist()---->>>>open db %s success!\n", PATH_OF_WIFIINFO_DB);
	char sql[512];
	char **dbResult;
	int nRow, nColumn;
	if(last_id > 0){
		sprintf(sql, "SELECT * FROM %s WHERE ID < %d ORDER BY ID DESC LIMIT %d;",NAME_OF_WIFIINFO_TABLE, last_id, MAX(count, 0));
	}else{
		sprintf(sql, "SELECT * FROM %s ORDER BY ID DESC LIMIT %d;",NAME_OF_WIFIINFO_TABLE, MAX(count, 0));
	}
	Log("db_select_wifilist()--->>>sql = %s\n", sql);
	ret = sqlite3_get_table(db, sql, &dbResult, &nRow, &nColumn, NULL);
	if (ret != SQLITE_OK ){
		sqlite3_free_table(dbResult);
		sqlite3_close(db);
		return NULL;
	}
	Log("The queried records nRow = %d\n", nRow);
	Log("The queried records nColumn = %d\n", nColumn);
	int index = nColumn;//skip table header
	int num = MIN(count, nRow);//get the min var between count and row
	int i;
	for(i = 0; i < num; i++){
		dbWifiList[i].db_id = atoi(dbResult[index++]);
		dbWifiList[i].wifiinfo.wifi_id = atoi(dbResult[index++]);
		dbWifiList[i].wifiinfo.wifi_matser_id = atoi(dbResult[index++]);
		sprintf(dbWifiList[i].wifiinfo.wifi_ssid, "%s", dbResult[index++]);
		sprintf(dbWifiList[i].wifiinfo.wifi_password, "%s", dbResult[index++]);
		sprintf(dbWifiList[i].wifiinfo.wifi_mac, "%s", dbResult[index++]);
		dbWifiList[i].wifiinfo.wifi_longitude= atof(dbResult[index++]);
		dbWifiList[i].wifiinfo.wifi_latitude = atof(dbResult[index++]);
		dbWifiList[i].wifiinfo.wifi_area_id = atoi(dbResult[index++]);
	}
	sqlite3_free_table(dbResult);
	sqlite3_close(db);
	return num;
}

/**
 * to judge the wifi is exist in the table according to the mac
 */
int l_is_wifiinfo_exist(sqlite3 *db, char *mac){
	if(db == NULL || strlen(mac) <= 0){
		return 0;
	}
	char sql[512];
	char **dbResult;
	int nRow = 0;
	sprintf(sql, "SELECT * FROM %s WHERE MAC = '%s';", NAME_OF_WIFIINFO_TABLE, mac);
	int ret = sqlite3_get_table( db, sql, &dbResult, &nRow, NULL, NULL);
	Log("l_is_wifiinfo_exist()--->>>the select row = %d\n", nRow);
	if(ret == SQLITE_OK && nRow > 0){
		sqlite3_free_table(dbResult);
		return 1;
	}else{
		sqlite3_free_table(dbResult);
		return 0;
	}
}

/**
 * local function:update the wifiinfo
 */
int l_db_update_wifiinfo(sqlite3 *db, WifiInfo wifiinfo){
	if(db == NULL){
		return 0;
	}
	int ret = 0;
	char sql[1024];
	sprintf(sql,
			"UPDATE %s SET WIFI_ID =%d, MAST_ID =%d, SSID ='%s', PASSWORD ='%s', LONGITUDE =%f, LATITUDE =%f, AREA_ID =%d WHERE MAC='%s';",
			NAME_OF_WIFIINFO_TABLE,wifiinfo.wifi_id, wifiinfo.wifi_matser_id, wifiinfo.wifi_ssid, wifiinfo.wifi_password,
			wifiinfo.wifi_longitude, wifiinfo.wifi_latitude, wifiinfo.wifi_area_id, wifiinfo.wifi_mac);
	Log("l_db_update_wifiinfo()--->>>sql = %s\n", sql);
	ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
	if(ret == SQLITE_OK){
		return 1;
	}else{
		return 0;
	}
}

/**
 * local insert the wifiinfo to the table
 */
int l_db_insert_wifiinfo(sqlite3 *db, WifiInfo wifiinfo){
	if(db == NULL){
		return 0;
	}
	int ret = 0;
	char sql[1024];
    sprintf(sql, "INSERT INTO %s (WIFI_ID, MAST_ID, SSID, PASSWORD, MAC, LONGITUDE, LATITUDE, AREA_ID) VALUES(%d,%d,'%s','%s','%s',%f,%f,%d);",
    		NAME_OF_WIFIINFO_TABLE, wifiinfo.wifi_id, wifiinfo.wifi_matser_id, wifiinfo.wifi_ssid, wifiinfo.wifi_password, wifiinfo.wifi_mac,
			wifiinfo.wifi_longitude, wifiinfo.wifi_latitude, wifiinfo.wifi_area_id);
    Log("l_db_insert_wifiinfo()--->>>sql = %s\n", sql);
    ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
	if(ret == SQLITE_OK){
		return 1;
	}else{
		return 0;
	}
}

int l_db_wifiinfo_execute_sql(char *sql){
	int ret = 0;
	sqlite3 *db = NULL;
	ret = sqlite3_open(PATH_OF_WIFIINFO_DB, &db); //open the wifiinfo database
	if(ret != SQLITE_OK){
		Log("l_db_wifiinfo_execute_sql()---->>>open db %s failed!", PATH_OF_WIFIINFO_DB);
		sqlite3_close(db);
		return NULL;
	}
	Log("l_db_wifiinfo_execute_sql()---->>>>open db %s success!\n", PATH_OF_WIFIINFO_DB);
    ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
	if(ret == SQLITE_OK){
		Log("execute sql success!--->%s\n", sql);
		sqlite3_close(db);
		return 1;
	}else{
		sqlite3_close(db);
		return 0;
	}
}

int db_delete_wifiinfo_by_id(int db_id){
	char sql[1024];
	sprintf(sql, "DELETE FROM %s WHERE ID = %d;", NAME_OF_WIFIINFO_TABLE, db_id);
	return l_db_wifiinfo_execute_sql(sql);
}

int db_delete_wifiinfo_by_mac(char *mac){
	char sql[1024];
	sprintf(sql, "DELETE FROM %s WHERE MAC = '%s';", NAME_OF_WIFIINFO_TABLE, mac);
	return l_db_wifiinfo_execute_sql(sql);
}
